using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace Sqlpager
{
	public class DbMySql: DbPage
	{
		MySqlConnection mConn;
		
		public DbMySql(string table,string columns)
			:base(table,columns)
		{
			Connect();
		}
		public DbMySql(string server, string user, 
		              string password,string database,
		              string remember)	
			:base(server,user,password,database,remember)
		{
			Connect();
		}
		public DbMySql(string server, string user, 
		              string password)	
			:base(server,user,password)
		{
			Connect();
		}
		
		public override bool Connect()
		{
			mConnected = false;
			if(String.IsNullOrEmpty(mConnString))
				return false;	
			using (mConn = new MySqlConnection(mConnString))
			{
				try{
					mConn.Open();	
					mConnected = true;
				}catch(MySqlException ex){
					mError = ex.Message;
				}				
			}
			return mConnected;
		}
		public override void SetConnectionString()
		{
			if(mDic.ContainsKey("server"))
				mConnString = "Server=" + mDic["server"];
			if(mDic.ContainsKey("user"))
				mConnString += ";User ID=" + mDic["user"];
			if(mDic.ContainsKey("password"))
				mConnString += ";Password=" + mDic["password"];
			if(mDic.ContainsKey("database"))
				mConnString += ";Database=" + mDic["database"];	 
			mConnString += ";Pooling=false";
		}
		protected override int GetTotal()
		{
			mSql = "select count(*) from " + mTable;
			if(mSearchText != "")
				mSql += " where " + mWhereSql;
			DataTable r = this.GetData(mSql);
			if(r!=null)
				return Int32.Parse("0"+r.Rows[0][0]);
			return 0;
		}
		public override DataTable GetData(string sql)
		{
			DataTable dt = new DataTable();		
			using (mConn = new MySqlConnection(mConnString)){
				try{
					mConn.Open();
					MySqlCommand cmd = 
						new MySqlCommand(sql,mConn);
					MySqlDataAdapter da = 
						new MySqlDataAdapter(cmd);
					da.Fill(dt);
				}catch(MySqlException ex){
					Console.WriteLine(ex.Message);
					dt = null;
				}
			}
			return dt;
		}
		protected override string CreateSql()
		{
			string sql = "select " + mColumns + " from " + mTable;
				if(mSearchText != "")
					sql += " where " + mWhereSql;
				sql += " order by " + mOrderbySql;
				sql += " limit " + mPrevious + "," + mRecords;
			return sql;	
		}
		protected override string CreateWhereSql()
		{
			string whereSql = mColumnList[0]+" like '%"+ mSearchText +"%'";
				for(int i=1; i<mColumnList.Length; i++)
					whereSql += " or "+ mColumnList[i] +
						" like '%"+ mSearchText +"%'";
			return whereSql;
		}
		public override int Execute(string sql)
		{			
			using (mConn = new MySqlConnection(mConnString)){
				mConn.Open();
				MySqlCommand cmd = new MySqlCommand(sql,mConn);
				int nrofilas = cmd.ExecuteNonQuery();
				cmd.Dispose();
				return nrofilas;
			}
		}
		
		public override Ticket Select(int number)
		{
			string sql =
				"select * from tickets where number="+number;
			DataTable dt = GetData(sql);
			Ticket t = null;
			if(dt!=null)
			{				
				DataRow r = dt.Rows[0];
				t = new Ticket();
				t.Number = Convert.ToInt32(r["number"]);
				t.Date = Util.CDate(r["date"]);
				t.Customer = r["customer"].ToString();
				t.Problem = r["problem"].ToString();
				t.Ip = r["ip"].ToString();
				t.Status = r["status"].ToString();
				t.Active = Convert.ToBoolean(r["active"]);
			}
			return t;
		}
		public override void Insert(Ticket t)
		{
			string sql = "insert into tickets("+
				"date,customer,problem,ip,status,active"+
				") values ('"+
				Util.SqlDate(t.Date) + "','"+
				t.Customer + "','"+
				t.Problem + "','"+
				t.Ip + "','"+
				t.Status + "','"+
				((t.Active)?"1":"0") + "')";
			Console.WriteLine(sql);
			Execute(sql);
		}
		public override void Update(Ticket t)
		{
			string sql = "update tickets set "+
				"date='"+ Util.SqlDate(t.Date) +
				"',customer='"+ t.Customer +
				"',problem='"+ t.Problem +
				"',ip='"+ t.Ip +
				"',status='"+ t.Status +
				"',active='"+ ((t.Active)?"1":"0")  +
				"' where number="+t.Number;
			Execute(sql);
					
		}
		public override void Delete(int number)
		{
			Execute("delete from tickets where number="+number);
		}

		
	}
}
